package com.linln.modules.lzgd.repository;

import com.linln.modules.lzgd.domain.BaseInfo;
import com.linln.modules.system.repository.BaseRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author 小懒虫
 * @date 2023/12/03
 */
public interface BaseInfoRepository extends BaseRepository<BaseInfo, Long> {

    @Query(value = "SELECT * FROM lzgd_base_infobase_info where status='1' and  jcbh=?",nativeQuery=true)
    BaseInfo findByJcbh(String jcbh);


    @Query(value = "SELECT sqs as sqs,count(*) as total,sum(case when sfzgdw='是' then 1 else 0 end)zgdwCount,sum(case when sfzgdw='否' then 1 else 0 end)wzgdwCount FROM lzgd_base_infobase_info where status='1' GROUP BY sqs ORDER BY  total DESC",nativeQuery=true)
    List<Map<String, String>> statTotalNewLZCount();

    /**
     * 统计总数
     * @return
     */
    @Query(value = "SELECT count(*) as total,sqs as sqs FROM lzgd_base_infobase_info where status='1' and sfzgdw='否' GROUP BY sqs ORDER BY  total DESC",nativeQuery=true)
    List<Map<String, String>> statUnLZCountBySQS();

    /**
     * 统计总数
     * @return
     */
    @Query(value = "SELECT count(*) as total,xsq as xsq FROM lzgd_base_infobase_info where status='1' and sfzgdw='否' GROUP BY xsq ORDER BY  total DESC",nativeQuery=true)
    List<Map<String, String>> statUnLZCountByXSQ();
}